

ALTER VIEW [dbo].[OTR_ReceiptsToImport]
AS
SELECT     TOP (100) PERCENT DV.Delivery_ID, DV.PumpNo, DV.LN, dbo.Grades.Grade_ID, dbo.Grades.Oil_Company_Code AS PLUCode, DV.Price, DV.Volume, DV.Amount, 
                      DV.DeliveryTime, DV.Receipt_Type,
                      Payment_Type=(CASE WHEN (DV.smOPT_ID>0 AND DV.smOPT_AuthID > 0) THEN 
                                                         CASE WHEN AUT.PaymentType=12 THEN 
                                                               (CASE AUT.CreditCardOrgNo 
                                                                                WHEN 11 THEN 91	-- Πιστωτική Κάρτα ΕΘΝΙΚΗ
                                                                                WHEN 12 THEN 92	-- Πιστωτική Κάρτα ΕΜΠΟΡΙΚΗ
                                                                                WHEN 14 THEN 93	-- Πιστωτική Κάρτα ALPHA	
                                                                                WHEN 17 THEN 94	-- Πιστωτική Κάρτα ΠΕΙΡΑΙΩΣ
                                                                                WHEN 26 THEN 95	-- Πιστωτική Κάρτα EUROBANK
                                                                                WHEN 28 THEN 96	-- Πιστωτική Κάρτα ΕΓΝΑΤΙΑ
                                                                                WHEN 43 THEN 97	-- Πιστωτική Κάρτα ΑΓΡΟΤΙΚΗ
                                                                                WHEN 84 THEN 98	-- Πιστωτική Κάρτα CITIBANK
                                                                                ELSE AUT.PaymentType END)
                                                             -- KTEO 2022-10-11 - TFS 135505
                                                             ELSE CASE WHEN AUT.PaymentType= 21 THEN DV.Payment_Type ELSE AUT.PaymentType END END
                                    ELSE DV.Payment_Type END) ,
                      DV.Receipt_State, DV.Receipt_Number, DV.TS_ReceiptIssued, DV.SFPSeries, DV.SFPNumber, DV.NetAmount, DV.VatAmount, DV.VatRate, DV.StoredInBO, (CASE WHEN LEN(ISNULL(DV.IButtonID, '')) > 0 THEN DV.IButtonID ELSE LF.CardID END) AS CardNumber, 
                      ISNULL(IB.IButtonID, '') AS AttendantIButton, DV.VatRegNo, DV.LisencePlate, DV.EuromatID, DV.EuromatCustCode, DV.EuromatInvNo, DV.EuromatInvDateTime, 
                      DV.smOPT_ID, DV.smOPT_AuthID, DV.Period_ID, PP.Period_Name, PP.Period_Create_TS, (CASE WHEN len(isnull(LF.TransactionID, '')) 
                      = 0 THEN '00000000-0000-0000-0000-000000000000' ELSE LF.TransactionID END) AS TransID, LF.CardType, LF.CardOwner AS ContactName, LF.TraderID, 
                      LF.ContactID, LF.RetailPriceList AS RetailCatalog, LF.RetailReceiptType AS RetailDocType, LF.RetailPaymentType, DV.NetFuelValue, 
                      DV.Amount - DV.NetFuelValue AS AllTaxValue, DV.TCVolume
					  --KTEO 2022-01-03 - RfE 129531 
					  --KTEO 2022-03-04 - RfE 131676
					 ,MaskedCard =(CASE WHEN LEN(AUT.MaskedCard)>0 THEN AUT.MaskedCard
									  ELSE CASE WHEN LEN(AUT.FleetCardNo)>0 THEN AUT.FleetCardNo 
												ELSE NULL END
												END)
					 ,CardAuthID=(CASE AUT.CardAuthID WHEN '' THEN NULL 
												   WHEN '0' THEN NULL 
												   ELSE AUT.CardAuthID END)
					 ,RRN=(CASE AUT.CreditCard_RRN WHEN '' THEN NULL 
												   WHEN '0' THEN NULL 
												   ELSE AUT.CreditCard_RRN END)
					 ,BankID=(CASE AUT.CreditCardOrgNo WHEN 0 THEN NULL ELSE AUT.CreditCardOrgNo END)
					 ,FleetCardType=(CASE AUT.FleetCardType WHEN 0 THEN NULL ELSE AUT.FleetCardType END)   
					 ,EAUT.OdometerValue -- FDEV-295352
					 ,OPTOdometerValue = dv.OdometerValue
					  ,((select ParameterValue from ParameterValues where ParameterValue_ID = 1638 
and Store_No = (select top 1 Site_id from Global_Settings) )  + '?TransacID=' + ltrim(DV.SFPSeries) + Cast(dv.SFPNumber as varchar(10))
+ '&Product=' +  case  dv.Grade_RegNo when '10' then '1' when '12' then '2' when '20' then '3' when '21' then '4' when '30' then '5' when '40' then '5' else '99'end   
+ '&SiteID=' + Cast((select top 1 Site_id from Global_Settings) as varchar(5))
+ '&Datetime=' +  ( select  SUBSTRING(DateStr, 6, 2) + SUBSTRING(DateStr, 9, 2)+  SUBSTRING(DateStr, 3, 2)  
	+ SUBSTRING(DateStr, 12, 2)  + SUBSTRING(DateStr, 15, 2)  from 
	(select convert(varchar(23) , dv.TS_ReceiptIssued, 126 )) as CA(DateStr)   )                               --Cast(format(dv.TS_ReceiptIssued, 'ddMMyyHHmm') as varchar(10))
+ '&ProductValue=' + Convert(varchar(10),dv.Amount ,1)
+ '&ProductAmount=' +   Convert(varchar(10),dv.Volume ,1) )
as VocQRCode
/*,SFPSignature AS TransSFPSignature, EipGuid AS EbooksExternalsystemId, AadeUid AS EbooksUID, AadeMark AS EbooksMark, AadeQRCode AS TransSFPQRCode*/
,(select top 1 Site_Number from Global_Settings) as BranchNo
FROM         (SELECT     Period_ID, Delivery_ID, Period_Date_ID, Period_Week_ID, Period_Month_ID, Period_Year_ID, PumpID, PumpNo, TransNo, LN, Delivery_Type, 
                                              Receipt_Type, Payment_Type, Receipt_State, Receipt_Number, Delivery_Age, GradeID, HoseID, Price, Volume, Amount, DeliveryTime, Att_Period_ID, 
                                              At_ID, TankID, TankTemp, TCVolume, TS_Finalized, TS_ReceiptIssued, Density, IButtonID, VatRegNo, LisencePlate, EuromatID, EuromatCustCode, 
                                              EuromatInvNo, EuromatInvDateTime, Journalled, smOPT_PreAuthAmount, smOPT_ID, smOPT_CardNo, smOPT_AuthID, PosId, NetAmount, VatAmount, 
                                              VatRate, SFPSeries, SFPNumber, SFPSignature, ManualFinalize, PrinterID, MeterVolume, MeterValue, MeterTrans, StoredInBO, LockedBy, LockedTS, 
                                              AVIAuthID, SA_MeterVolume, SA_MeterValue, SA_MeterTrans, TS_REP, OdometerValue, TS_Gsis_Sent, Gsis_Sent, Gsis_SignId, SentToNamos, 
                                              Pump_Reg_No, Nozzle_Reg_No, Grade_RegNo, Tank_RegNo, Tank_GSIS_No, PriceLevel, AvailableForBackOffice, DeliveryTimestamp, ETAuthID, 
                                              LoyaltyEventID, NetFuelValue/*, EipGuid, AadeUid, AadeMark, AadeQRCode*/
                       FROM          dbo.Deliveries
                       WHERE      (DeliveryTime > GETDATE() - 5)) AS DV INNER JOIN
                      dbo.Grades ON DV.GradeID = dbo.Grades.Grade_ID LEFT OUTER JOIN
                      dbo.IButtons AS IB ON DV.At_ID = IB.Attendant_ID LEFT OUTER JOIN
                      dbo.Periods AS PP ON DV.Period_ID = PP.Period_ID LEFT OUTER JOIN
                      dbo.LoyaltyFleetInterface AS LF ON DV.LoyaltyEventID = LF.EventID LEFT OUTER JOIN
					  dbo.smOPT_Authorizations AUT ON DV.smOPT_ID=AUT.OPTID and DV.smOPT_AuthID=AUT.AuthorizationID LEFT OUTER JOIN
					  dbo.EuromatAuthorizations EAUT ON DV.Delivery_ID=EAUT.DeliveryID
WHERE     (DV.Receipt_Type IN (41, 42, 45, 46)) AND (DV.StoredInBO = 0) AND (DV.Receipt_State = 0)
ORDER BY DV.DeliveryTime, DV.Delivery_ID





GO


